/**
 * 
 */
package vehicleService;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import constants.Constants;
import vehicleEntity.VehicleRentCostEntity;

/**
 * @author Yokesh Kumar
 *
 */
public class SetRateService {
	private Connection connection;
	private PreparedStatement statement;

	public SetRateService(){
		try {
			Class.forName(Constants.JDBC_DRIVER);
			connection = DriverManager.getConnection(Constants.DB_URL,Constants.USER_NAME, Constants.PWD);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


	}
	/**
	 * This method fetches the list of vehicle class based on vehicle type
	 * @param vehicleClass
	 * @Pre Vehicle class value retrieved from setrate logic class
	 * @Post vehicleType is fetched out from the database
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet fetchVehicleType(String vehicleClass) throws SQLException{
		ResultSet rs = null;
		String query = "select vehicleType from vehicle_rent_cost where vehicleClass = ?";
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, vehicleClass);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	/**
	 * This method fetches rates based on vehicle class
	 * @param vehicleType
	 * @Pre VehicleType value is retrieved from serRateLogic class
	 * @Post vehicle rent cost table cloumn values are passed then to setRatelogic class
	 * @return none
	 * @throws SQLException 
	 */
	public ResultSet fetchVehicleRates(String vehicleType) throws SQLException{
		ResultSet rs = null;
		String query = "select * from vehicle_rent_cost where vehicleType = ?";
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, vehicleType);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	/**
	 * This method updates the value of vehicle class rent rates
	 * @param rentCost
	 * @Pre rentCost values are retrieved from the setRateLogic class
	 * @Post Updates the vehiclerentcost table
	 * @throws SQLException 
	 */
	public void updateVehicleRates(VehicleRentCostEntity rentCost) throws SQLException{
		String query = "Update vehicle_rent_cost set kmRate = ?,hourlyRate = ?,dailyRate = ?,weeklyRate = ?,hourlyInsRate = ?,dailyInsRate = ?,weeklyInsCost = ?,kmLimit = ? where vehicleType = ?";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setDouble(1,rentCost.getKmRate());
			statement.setDouble(2,rentCost.getHourlyRate());
			statement.setDouble(3,rentCost.getDailyRate());
			statement.setDouble(4,rentCost.getWeeklyRate());
			statement.setDouble(5,rentCost.getHourlyInsRate());
			statement.setDouble(6,rentCost.getDailyInsRate());
			statement.setDouble(7,rentCost.getWeeklyInsRate());
			statement.setInt(8,rentCost.getKmLimit());
			statement.setString(9,rentCost.getVehicleType());
			statement.executeUpdate();
			connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
	}

	/**
	 * This method fetches the current fuel rate
	 * @Param none
	 * @Pre fuel rate values are set initially in the setRateEntity class
	 * @Post result set values are  passed to the setRatelogic class
	 * @return none
	 * @throws SQLException 
	 */
	public ResultSet fetchFuelRate() throws SQLException{
		ResultSet rs = null;
		String query = "SELECT fuelRate FROM fuel_rate order by rateChangeDate desc limit 1";
		try {
			statement = connection.prepareStatement(query);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	/**
	 * This method updates the new fuel rate
	 * @param fuelRate
	 * @Pre fuelrate values are retrieved from the setRateLogic class
	 * @Post Valus are inserted into the fuelrate table in the datbase
	 * @return none
	 * @throws SQLException 
	 */
	public void updateFuelRate(Double fuelRate) throws SQLException{
		String query = "INSERT INTO fuel_rate values(current_date,?)";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setDouble(1, fuelRate);
			statement.executeUpdate();
			connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
	}

	public void closeConnection(){
		try{
			if(statement != null){
				statement.close();
			}
			if(connection != null){
				connection.close();
			}
		}
		catch(SQLException sqlExec){
			sqlExec.printStackTrace();
		}
	}

}
